package com.QBsoft.persistencia;

import java.net.MalformedURLException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.QBsoft.commons.resources.WsResourcesConexion;

public class Conexion {

	private Connection con = null;
	private Statement st = null;

	public void getConexion() throws SQLException, ClassNotFoundException, MalformedURLException {
		try {
			WsResourcesConexion wsResourcesConexion=new WsResourcesConexion();
			DriverManager
					.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
			con = DriverManager.getConnection(
					"jdbc:sqlserver://"
							+ wsResourcesConexion.getString("com.QBsoft.conexion.host")
							+ ":"
							+ wsResourcesConexion.getString("com.QBsoft.conexion.puerto")
							+ ";databaseName="
							+ wsResourcesConexion.getString("com.QBsoft.conexion.db"),
							wsResourcesConexion.getString("com.QBsoft.conexion.user"),
							wsResourcesConexion.getString("com.QBsoft.conexion.pwd"));
		} catch (SQLException ex) {
			throw ex;
		}
	}

	public void cierraConexion() throws SQLException {
		try {
			if (con != null) {
				con.close();
				con = null;
			}
		} catch (SQLException ex) {
			throw ex;
		}
	}

	public void iniciaTransaccion() throws SQLException {
		try {
			con.setAutoCommit(false);
		} catch (SQLException ex) {
			throw ex;
		}
	}

	public void cancelaTransaccion() throws SQLException {
		try {
			con.rollback();
			System.out.println("se realizo rollback:");
		} catch (SQLException ex) {
			
			throw ex;
		}
	}

	public void ejecutaTransaccion() throws SQLException {
		try {
			con.commit();
		} catch (SQLException ex) {
			throw ex;
		}
	}

	public void insertar(String sql) throws SQLException {
		try {
			st = con.createStatement();
			st.execute(sql);
		} catch (SQLException ex) {
			cancelaTransaccion();
			throw ex;
		} finally {
			if (st != null) {
				st.close();
			}
		}

	}
	public void cerrarStament(){
		try {
			if (st != null) {
				st.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public ResultSet obtenerDatos(String sqlString) throws SQLException{
		ResultSet resultado;
		try{
			st=con.createStatement();
			resultado=st.executeQuery(sqlString);
		}catch(SQLException ex){
			if (st != null) {
				st.close();
			}
			throw ex;
		}
		return resultado;
	}

}
